#!/usr/bin/python3
# -*- coding:utf-8 -*-
""""
@Author: lijk34925
@Time: 2023/4/25 13:46
@File: MakeSql.py
"""
import re

from jsonpath import jsonpath

from BladeFerm.Lib.Common import logger
from BladeFerm.databases.DealSql.fields_map import FieldsMap
from BladeFerm.utils.jsonOperation import JsonOperation


class MakeSql:
    def __init__(self):
        self.js = JsonOperation()
        self.fm = FieldsMap()
        self.sql_text = ""
        self.params = {}
        self.ui_expect = None

    def set_aml_int_date(self, date):
        self.fm.aml_ini_date = date

    def sql_update_field(self, sql_text: str, params: dict) -> object:
        """
        sql语句，使用jinjia2替换变量值
        Args:
            sql_text: sql语句，值的变量表达方式 {{变量名}}
            params: 变量的字典参数

        Returns:
            sql语句
        """
        sql_text = self.fm.get_parse_result(sql_text, params)
        logger.debug(f'sql_text :{sql_text}')
        self.sql_text = sql_text
        return sql_text

    def __read_json_raw_data(self, file_path: str, update_params) -> dict:
        content = self.js.read_json(file_path, 'utf-8')
        if content and isinstance(content, list):
            self.params = content[0]
        elif content and isinstance(content, dict):
            self.params = content
        else:
            self.params = {}
        self.params.update(update_params)
        return self.params


    def __splice_insert_sql(self, table_name):
        keys = self.params.keys()
        values = self.params.values()
        keys_str = ', '.join(list(keys))
        values2 = []
        for v in list(values):
            if isinstance(v, str):
                values2.append(f'"{v}"')
            elif v is None:
                values2.append('NULL')
            else:
                values2.append(str(v))
        values_str = ', '.join(values2)
        sql_text = f"insert into {table_name}({keys_str}) values ({values_str});"
        logger.info(f'insert sql : {sql_text}')
        self.sql_text = sql_text
        return sql_text

    @staticmethod
    def __case_text(case_params: dict) -> str:
        link_flag = case_params.get("logic", "and")
        if case_params:
            where_texts = " where "
            where_list = []
            for k, v in case_params.items():
                value_str = ', '.join([f"'{unit.strip()}'" for unit in v.split(',')])
                where_list.append(f'{k} in ({value_str})')
            where_texts += f' {link_flag} '.join(where_list)
            where_texts += ";"
        else:
            where_texts = ";"
        return where_texts

    def __splice_update_sql(self, table_name: str, set_params: dict, case_params: dict = None):
        set_texts = ', '.join([f"{k}='{v}'" for k, v in set_params.items()])
        sql_text = f"update {table_name} set {set_texts}"
        sql_text += self.__case_text(case_params)
        logger.info(f'update sql : {sql_text}')
        self.sql_text = sql_text
        return sql_text


    def __splice_delete_sql(self, table_name: str, case_params: dict = None):
        if case_params:
            where_text = self.__case_text(case_params)
            sql_text = f"delete from {table_name}{where_text}"
        else:
            sql_text = ""
        logger.info(f"delete sql : {sql_text}")
        self.sql_text = sql_text
        return sql_text

    def __splice_select_sql(self, table_name: str, keys: str, case_params: dict = None):
        where_text = self.__case_text(case_params)
        sql_text = f"select {keys} from {table_name}{where_text}"
        logger.info(f'select sql : {sql_text}')
        self.sql_text = sql_text
        return sql_text

    def splice_sql(self, **kwargs) -> str:
        """
        拼接sql
        Args:
            **kwargs:
                - sql_json_path： insert所需的初始数据
                - table_name: 表名称
                - sql_type: sql的类型，eg：insert，Delete, UPDATE，select
                - select_keys: select所需的key，eg：k1,k2,k3

        Returns:
            - slq_text: sql语句
        """
        table_dict = kwargs.get('table_params')
        sql_json_path = table_dict.get('sql_json_path', '')
        table_name = table_dict.get('table_name', 'dual')
        sql_type = table_dict.get('sql_type', 'insert')
        select_keys = kwargs.get('select_keys', '*')
        common_params = kwargs.get('common_params', {})
        case_params = kwargs.get('case_params', {})
        if sql_type.upper() == 'INSERT':
            self.__read_json_raw_data(sql_json_path, common_params)
            self.__splice_insert_sql(table_name)
        elif sql_type.upper() == 'DELETE':
            self.__splice_delete_sql(table_name, case_params)
        elif sql_type.upper() == 'UPDATE':
            self.__splice_update_sql(table_name, common_params, case_params)
        elif sql_type.upper() == 'SELECT':
            self.__splice_select_sql(table_name, select_keys, case_params)
        else:
            self.sql_text = f"暂不支持{sql_type}，当前支持 [insert，delete，update，select]。"
        return self.sql_text


class SQLCheck:
    def __init__(self):
        self.db_app = None
        self.raw_sql = ""
        self.new_sql = ""
        self.table_name = ""
        self.field_names = list()
        self.values = list()
        self.map = dict()
        self.data_error = list()

    def get_basic_detail(self, sql, filter_flag="*"):
        """
        获取sql语句的基本信息
        :param filter_flag:
        :param sql:
        :return:
        """
        self.raw_sql = sql
        if re.search(filter_flag, sql, flags=re.IGNORECASE) and re.match("insert into", sql, flags=re.IGNORECASE):
            # 正则表达式来匹配INTO后的表名和VALUES前的字段名
            pattern = r"INSERT INTO (\S+)\s*\(([^)]*)\)\s*VALUES"
            # 使用re.search来查找匹配项
            match = re.search(pattern, sql, re.I|re.M)
            if match:
                self.table_name = match.group(1)  # INTO后的表名
                field_names = match.group(2).split(',')  # VALUES前的字段名列表，以逗号分隔
                # 去除字段名两端的空格
                self.field_names = [name.strip() for name in field_names]
            values_match = re.split(r"values", sql, 1, flags=re.I|re.M)
            if values_match:
                values_str = values_match[1].strip().rstrip(";")
                values_str = (values_str.replace("null", "NULL").
                              replace("NULL", "'NULL'").
                              replace('\n', '\\n'))
                self.values = list(eval("(" + values_str + ")"))

    def get_table_dict(self):
        """
        获取表数据，返回k-v结构
        :return:
        """
        if self.field_names and len(self.field_names) == len(self.values):
            self.map = dict(zip(self.field_names, self.values))
        logger.info(f"get_table_dict : {self.map}")
        return self.map

    def __check_is_null(self, value, unit: dict):
        """
        检查字段是否为空
        :param value:
        :param unit:
        :return:
        """
        is_nullable = unit.get("IS_NULLABLE", 'N')
        if is_nullable == 'Y':
            result = True
        else:
            result = False if value is None or value == 'NULL' or value == '' else True
        if not result:
            err = f"字段 {unit['COLUMN_NAME']} 不能为空! 实际值为 {value}"
            self.data_error.append(err)
            logger.warning(err)
        return result

    def __check_data_length(self, value, unit: dict):
        """
        检查字段长度
        :param value:
        :param unit:
        :return:
        """
        data_length = unit.get("DATA_LENGHT", 2)
        data_type = unit.get("DATA_TYPE", 'NUMBER')
        if value and re.search(r"NUMBER|CHAR", data_type, re.I) and len(str(value)) > data_length:
            result = False
        else:
            result = True
        if not result:
            err = f"字段 {unit['COLUMN_NAME']} 长度不能超过 {data_length}! 实际值为 {value}"
            self.data_error.append(err)
            logger.warning(err)
        return result

    def __check_data_type_is_number(self, value, unit: dict):
        """
        检查字段是否为数字
        :param value:
        :param unit:
        :return:
        """
        def _judge_is_number(val, is_null):
            if not is_null and val == 'NULL':
                return False
            elif is_null and val == 'NULL':
                return True
            else:
                try:
                    float(val)
                    return True
                except ValueError:
                    return False
        data_type = unit.get("DATA_TYPE", 'NUMBER')
        is_null_flag = True if unit.get("IS_NULLABLE", 'N') == 'Y' else False
        if value and re.search(r"NUMBER", data_type, re.I) and (not _judge_is_number(value, is_null_flag)):
            result = False
        else:
            result = True
        if not result:
            err = f"字段 {unit['COLUMN_NAME']} 类型为非数字! 实际值为 {value}"
            self.data_error.append(err)
            logger.warning(err)
        return result

    def check_table_data_right(self, env):
        """
        检查表数据是否正确
        :param env:
        :return:
        """
        self.data_error = []
        user, tn = self.table_name.split(".")
        if re.search(r"mysql|auto|tdsql", env, re.I):
            query_sql = f"SELECT ORDINAL_POSITION as COLUMN_ID, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH as DATA_LENGHT FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME = upper('{tn}') and TABLE_SCHEMA = upper('{user}') ORDER BY COLUMN_ID ASC"
        else:
            query_sql = f"SELECT COLUMN_ID, COLUMN_NAME, DATA_TYPE, nullable as IS_NULLABLE, data_length AS DATA_LENGHT FROM ALL_TAB_COLUMNS WHERE table_name = UPPER('{tn}') ORDER BY COLUMN_ID ASC"
        sql_lines = [query_sql]
        logger.info(f'SQL语句:{sql_lines}')
        table_dict = self.db_app.get_run_sql_result(sql_lines)
        for key, value in self.map.items():
            unit = jsonpath(table_dict, f"$..[?(@.COLUMN_NAME=='{key.upper()}')]")
            logger.info(f"字段:{key},值：{value},类型: {unit}")
            if unit:
                self.__check_is_null(value, unit[0])
                self.__check_data_length(value, unit[0])
                self.__check_data_type_is_number(value, unit[0])
        logger.info(f"校验完成，存在 {len(self.data_error)} 个错误:{self.data_error}")
        return self.data_error

    def remove_target_key(self, filter_flag: str = "_time"):
        """
        去除时间相关字段
        :param filter_flag:
        :return:
        """
        # 去除目标字段
        new_keys = list()
        new_values = list()
        if len(self.field_names) > 0 and len(self.field_names) == len(self.values):
            for index, key in enumerate(self.field_names):
                if re.search(filter_flag, key, re.I):
                    continue
                else:
                    new_keys.append(key)
                    new_value = self.values[index]
                    new_values.append(new_value)
            new_key_str = ",".join(new_keys)
            new_values_str = str(new_values)[1:-1].replace('"', "'")  # 修复bug，Oracle不能出现双引号
            new_sql = f"INSERT INTO {self.table_name}({new_key_str}) VALUES ({new_values_str})"
            self.new_sql = new_sql.replace("'NULL'", "NULL")
        else:
            self.new_sql = self.raw_sql
        logger.info(f"remove_target_key : {self.new_sql}")
        return self.new_sql
